﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;

using System.Text;
using KPIS.DBM;
using KPIS.GERP.ESS.MODEL;

namespace KPIS.GERP.ESS.DAL
{
    public class D_HrmEmployees
    {
        public string GetEmployeeFullName(int Sequence)
        {
            string strSQL;
            string result = "";
            DataTable dt = null;
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@Sequence", Sequence);

                strSQL = @"SELECT CONCAT(np.PREFIX_NAME, e.FIRST_NAME, ' ', e.LAST_NAME) AS FULL_NAME
                     FROM HRM_PS_EMPLOYEES e
                          LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ
                     WHERE e.EMP_SEQ = @Sequence ";

                dt = dbManager.ExecuteDataTable(CommandType.Text, strSQL);
                if(dt.Rows.Count > 0)
                {
                    result = Convert.ToString(dt.Rows[0]["FULL_NAME"]);
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
            return result;
        }
        
        public DataSet GetEmployeeInfo(int Sequence, string SearchText)
        {
            string strSQL;
            DataSet ds = null;
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@Sequence", Sequence);
                dbManager.AddParameters(2, "@SearchText", SearchText);

                string whereSearch = "";
                if(!string.IsNullOrEmpty(SearchText))
                {
                    whereSearch = @" AND (e.FIRST_NAME LIKE CONCAT('%', @SearchText , '%')
                                      OR e.LAST_NAME LIKE CONCAT('%', @SearchText , '%')
                                      OR c.CLASS_NAME LIKE CONCAT('%', @SearchText , '%')
                                      OR p.POS_NAME LIKE CONCAT('%', @SearchText , '%')
                                      OR pj.JOB_SITE_NAME LIKE CONCAT('%', @SearchText , '%')
                                 ) ";
                }
                strSQL = @"SELECT e.EMP_SEQ, e.NAME_PREFIX_SEQ, np.PREFIX_NAME, e.FIRST_NAME, e.LAST_NAME
                         , e.FIRST_NAME_ENG, e.LAST_NAME_ENG
                         , CONCAT(np.PREFIX_NAME, e.FIRST_NAME, ' ', e.LAST_NAME) AS FULL_NAME
                         , CONCAT(coalesce(np.PREFIX_NAME_ENG, ''), e.FIRST_NAME_ENG, ' ', e.LAST_NAME_ENG) AS FULL_NAME_ENG
                         , e.GENDER_SEQ, e.RELIGION_SEQ, e.RACE_NAT_SEQ, e.NAT_SEQ, e.BIRTH_DATE, e.EMP_TYPE_SEQ
                         , e.CLASS_SEQ, e.SALARY_LEVEL_SEQ, e.SALARY, e.POS_SEQ, e.WORKING_JOB_SITE_SEQ
                         , e.DATE_ADMITTED, e.DATE_WORK_START, e.DATE_QUIT, e.EMP_STATUS_SEQ, e.EMP_SEC_SEQ
                         , e.CITIZEN, e.TAX_PAYER , e.SOCIAL, e.OFFICIAL, e.BLOOD_GROUP_SEQ, e.PROBATION_DATE_END, e.DEATH_DATE
                         , e.DATE_LATEST_POS, e.DATE_LATEST_CLASS, e.DATE_LATEST_WORKING_JOB_SITE, e.FUND_MEMBER
                         , p.POS_REF, p.POS_NAME, p.JOB_SITE_SEQ, pj.JOB_SITE_NAME
                         , c.CLASS_NAME, wj.JOB_SITE_NAME AS WORKING_JOB_SITE_NAME, et.EMP_TYPE_NAME
                         , gen.GENDER_NAME, re.RELIGION_NAME, rnat.NAT_NAME AS RACE_NAT_NAME, nat.NAT_NAME
                         , es.EMP_STATUS_NAME, f.POS_TYPE_SEQ, b.BLOOD_GROUP_NAME, ms.MAR_STAT_NAME
                         , CASE WHEN MONTH(e.BIRTH_DATE) >= 10 AND DAY(e.BIRTH_DATE) > 1
                              THEN CAST(concat( YEAR(e.BIRTH_DATE)  61, '-', '10-01') AS DATE) 
                              ELSE CAST(concat( YEAR(e.BIRTH_DATE)  60, '-', '10-01') AS DATE) 
                              END AS RETIRE_DATE
                         , f.FIELD_POS_NAME, ex.EXEC_POS_NAME
                         , CONCAT(p.POS_REF, ' - ', p.POS_NAME) AS POS_TEXT
                     FROM HRM_PS_EMPLOYEES e
                          LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ
                          LEFT JOIN HRM_PS_EMPLOYEE_TYPES et ON e.EMP_TYPE_SEQ = et.EMP_TYPE_SEQ
                          LEFT JOIN HRM_MP_CLASSES c ON e.CLASS_SEQ = c.CLASS_SEQ
                          LEFT JOIN HRM_MP_POSITIONS p ON e.POS_SEQ = p.POS_SEQ
                          LEFT JOIN HRM_MP_FIELD_POSITIONS f ON p.FIELD_POS_SEQ = f.FIELD_POS_SEQ
                          LEFT JOIN HRM_MP_EXEC_POSITIONS ex ON p.EXEC_POS_SEQ = ex.EXEC_POS_SEQ
                          LEFT JOIN HRM_MP_JOB_SITES pj ON p.JOB_SITE_SEQ = pj.JOB_SITE_SEQ
                          LEFT JOIN HRM_MP_JOB_SITES wj ON e.WORKING_JOB_SITE_SEQ = wj.JOB_SITE_SEQ
                          LEFT JOIN HRM_PS_EMPLOYEE_STATUSES es ON e.EMP_STATUS_SEQ = es.EMP_STATUS_SEQ
                          LEFT JOIN MAS_GENDERS gen ON e.GENDER_SEQ = gen.GENDER_SEQ
                          LEFT JOIN MAS_RELIGIONS re ON e.RELIGION_SEQ = re.RELIGION_SEQ
                          LEFT JOIN MAS_NATIONALITIES rnat ON e.RACE_NAT_SEQ = rnat.NAT_SEQ
                          LEFT JOIN MAS_NATIONALITIES nat ON e.NAT_SEQ = nat.NAT_SEQ
                          LEFT JOIN MAS_BLOOD_GROUPS b ON e.BLOOD_GROUP_SEQ = b.BLOOD_GROUP_SEQ
                          LEFT JOIN MAS_MARITAL_STATUSES ms ON e.MAR_STAT_SEQ = ms.MAR_STAT_SEQ
                     WHERE e.RECORD_STATUS = @ActiveRecord
                     AND e.EMP_SEQ = @Sequence " + whereSearch + @"
                     ORDER BY p.POS_REF  1;";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);

            }
            catch 
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
            return ds;
        }

        public DataSet GetBgmProjOperPerList(Nullable<int> bgm_proj_oper_seq, string SearchText)
        {
            string strSQL;
            DataSet ds = null;
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(2, "@bgm_proj_oper_seq", bgm_proj_oper_seq);
                dbManager.AddParameters(3, "@SearchText", "%" + SearchText + "%");

                strSQL = "SELECT distinct *"

                    + "FROM (SELECT e.EMP_SEQ"
                        + ", CONCAT(np.PREFIX_NAME, e.FIRST_NAME, ' ', e.LAST_NAME) AS EMP_NAME"
                    + " FROM HRM_PS_EMPLOYEES e"
                    + " LEFT JOIN hrm_ps_employee_statuses es on e.emp_status_seq = es.emp_status_seq"
                    + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                    + " LEFT JOIN BGM_PROJECT_OPERATION_PERSON opper ON e.emp_seq = opper.emp_seq"
                        + " AND opper.RECORD_STATUS <> @DeleteRecord"
                        + " AND opper.bgm_proj_oper_seq = @bgm_proj_oper_seq"
                    + " WHERE e.RECORD_STATUS = @ActiveRecord"
                    + " AND es.active = '1'"
                    + " AND opper.BGM_PROJ_OPER_PER_SEQ IS NULL"
                    + ") t"
                    + " where 0=0"
                    + (SearchText != null ? " and t.EMP_NAME like @SearchText" : "");

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);

            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
            return ds;
        }

        public DataSet GetMoneyRequestTravelList(Nullable<int> money_req_seq, string SearchText)
        {
            string strSQL;
            DataSet ds = null;
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(2, "@money_req_seq", money_req_seq);
                dbManager.AddParameters(3, "@SearchText", "%" + SearchText + "%");

                strSQL = "SELECT distinct *"

                    + "FROM (SELECT e.EMP_SEQ"
                        + ", CONCAT(np.PREFIX_NAME, e.FIRST_NAME, ' ', e.LAST_NAME) AS EMP_NAME"
                    + " FROM HRM_PS_EMPLOYEES e"
                    + " LEFT JOIN hrm_ps_employee_statuses es on e.emp_status_seq = es.emp_status_seq"
                    + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                    + " LEFT JOIN FIM_MONEY_REQUEST_TRAVELERS trav ON e.emp_seq = trav.emp_seq"
                        + " AND trav.RECORD_STATUS <> @DeleteRecord"
                        + " AND trav.money_req_seq = @money_req_seq"
                    + " WHERE e.RECORD_STATUS = @ActiveRecord"
                    + " AND es.active = '1'"
                    + " AND trav.traveler_seq IS NULL"
                    + ") t"
                    + " where 0=0"
                    + (SearchText != null ? " and t.EMP_NAME like @SearchText" : "");

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);

            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
            return ds;
        }

        public DataTable GetInfo(int seq)
        {
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql;
                sql = " select e.emp_seq as sequence, e.pos_seq as t_pos_seq, e.class_seq as t_class_seq"
                    + ", e.working_job_site_seq, mp.exec_pos_seq"
                    + " ,CONCAT( coalesce(np.prefix_name, ''), e.first_name,' ',e.last_name ) full_name"

                    + ", mp.pos_name, ex.exec_pos_name, c.class_name, j.job_site_name"

                    + " from hrm_ps_employees e"
                    + " left join hrm_mp_positions mp on e.pos_seq = mp.pos_seq"
                    + " left join hrm_mp_exec_positions ex on mp.exec_pos_seq = ex.exec_pos_seq"
                    + " left join hrm_mp_classes c on e.class_seq = c.class_seq"
                    + " left join hrm_mp_job_sites j on e.working_job_site_seq = j.job_site_seq"
                    + " left join mas_name_prefixes np on e.name_prefix_seq = np.name_prefix_seq"

                    + " where e.emp_seq = " + seq;

                DataSet ds;
                DataTable dt;
                ds = new DataSet();
                dbManager.Open();
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception ex)
            {

            }
            finally
            {
                dbManager.Dispose();
            }
            return null;
        }

        public DataTable ListEmployeeByType(string strEmpTypeSeq, string strJobSiteSeq)
        {
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql;
                sql = " select e.emp_seq as sequence, e.pos_seq as t_pos_seq, e.class_seq as t_class_seq"
                    + ", mp.pos_ref as ref_no, mp.pos_name as posName, mc.class_name as className, js.job_site_name as jobsiteName"
                    + ", ex.exec_pos_name"
                    + " ,CONCAT( np.prefix_name, e.first_name,' ',e.last_name ) full_name"
                    + " from hrm_ps_employees e"
                    + " left join hrm_mp_positions mp on e.pos_seq = mp.pos_seq"
                    + " left join hrm_mp_exec_positions ex on mp.exec_pos_seq = ex.exec_pos_seq"
                    + " left join hrm_mp_job_sites js on e.working_job_site_seq = js.job_site_seq"
                    + " left join hrm_mp_classes mc on e.class_seq = mc.class_seq"
                    + " left join mas_name_prefixes np on e.name_prefix_seq = np.name_prefix_seq"
                    + " left join hrm_ps_employee_statuses es on e.emp_status_seq = es.emp_status_seq"

                    + " where es.active = '1'";
                if (strEmpTypeSeq != "")
                {
                    sql += " and e.emp_type_seq in " + strEmpTypeSeq;
                }
                if (strJobSiteSeq != "")
                {
                    sql += " and e.working_job_site_seq = " + strJobSiteSeq;
                }
                sql += " order by e.first_name, e.last_name";

                DataSet ds;
                DataTable dt;
                ds = new DataSet();
                dbManager.Open();
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0];
                
                return dt;
            }
            catch (Exception ex)
            {

            }
            finally
            {
                dbManager.Dispose();
            }
            return null;
        }
    }
}
